# -*- coding: utf-8 -*-
"""
author : heting
date : 2018/6/26 17:24
desc : 
"""
from MyComFiles.MyPublic import *
from MyComFiles.qiYuan.user import User
from MyComFiles.qiYuan.keyMap import keyMap

engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/qy_datasys?charset=utf8', echo=False)

os.chdir('./')
workPath = os.getcwd()
print('workPath: ', os.getcwd())
time_start = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
 
def monthToQuarter(x):
    if x[-2:] in ['01','02','03']:
        return 'Q1'
    if x[-2:] in ['04','05','06']:
        return 'Q2'
    if x[-2:] in ['07','08','09']:
        return 'Q3'
    if x[-2:] in ['10','11','12']:
        return 'Q4'
    
"""初始化一个结果数据框--兴业尽调账龄分析"""
dataDf = DataFrame()
startDate = '2017-05'
baseMonthList  = newMonthList(startDate)[:]
dataDf['month'] = baseMonthList
dataDf['year'] = [item[2:4] for item in baseMonthList]
dataDf['quarter'] = dataDf['year']+dataDf['month'].map(monthToQuarter)
quarterSet = list(dataDf['quarter'].drop_duplicates())[:-1]
dataDf['xTerm'] = dataDf['quarter'].map(lambda x:sum(dataDf['quarter']>x))

DataList = []
"""查询每个季度放款明细-用户id"""
for item in quarterSet[:]:
#    item = '17Q4'
    monthList = list(dataDf['month'][dataDf['quarter']==item])
    termList = list(dataDf['month'][dataDf['quarter']>item])
    monthStr = ','.join("'%s'"%x for x in monthList)

    """查询每个季度放款金额及人数"""
    sql = """
            -- 
            -- 
            SELECT
            t.`month`,
            10000*t.loan_amount loan_amount,
            t.succ
        FROM
            oper_month t
        WHERE
            t. MONTH IN (%s);
    """%monthStr
    dfTemp = pdReadSqlHost(sql)
    quarterAmount = dfTemp['loan_amount'].sum()
    succCnt = dfTemp['succ'].sum()

    i = 1
    for term in termList[:]:
        semiMap = {}
        """查询历史数据回滚表"""
        sql = """
                -- 
                -- 
                SELECT
            			left(t.queryDate,7) queryMonth,t.*
                FROM
                    qy_temp.history_repay_plan t
                where 
                t. STATUS in('overdue')
                and left(t.loan_at,7) in (%s)
                and left(t.queryDate,7) = '%s'
                ORDER BY
                    t.queryDate
        """ % (monthStr,term)
#        print(sql)
        df = pdReadSqlHost(sql)
        overdueUserList = list(df['user_id_num'].drop_duplicates())
        overdueUserStr = '""'
        overdueCnt = len(overdueUserList)
        semiMap['overdue_benjin'] = 0
        semiMap['overdueUserLoanBenJin'] = 0
        semiMap['overdue_already_pay_ben_jin'] = 0
        if len(overdueUserList):
            overdueUserStr = ','.join("'%s'" % x for x in overdueUserList)
            """查询数据库还款计划表 放款日期为monthStr 且当时逾期的UserList"""
            sql = """
                    -- 查询该客户还款计划表明细记录 
                    -- 
                    SELECT
                        t.user_name,
                        t.user_id_num,
                        t.cellphone,
                        t.loan_at,
                        t.loan_amount,
                        t.pay_ben_jin,
                        t.already_pay_ben_jin,
                        t.pay_loan_interest,
                        t.already_pay_loan_interest,
                        t.pay_platform_interest,
                        t.already_pay_platform_interest,
                        t.early_settled_handle_charge,
                        t.counted_should_pay,
                        t.overdue_days,
                        t.total_pay,
                        t.already_payed,
                        t.total_overdue_amount,
                        t.already_pay_overdue_amount,
                        t.end_date,
                        t.repay_date,
                        t.term,
                        t.contract_id,
                        t.loan_term,
                        t.repay_bank_card_num,
                        t.repay_bank_name,
                        t.overdue_status,
                        t.status
                    FROM
                        qy_repayment_prod.repay_plans t
                    where t.user_id_num in (%s)
                    and left(t.loan_at,7) in (%s)
                    and left(t.repay_date,7)<='%s';
                """ % (overdueUserStr, monthStr, term)
#            print(sql)
            dfOverdue = pdReadSql(sql)
            overdueDf = dfOverdue[['user_id_num','loan_amount']].drop_duplicates()
            overdueUserLoanBenJin = overdueDf['loan_amount'].sum()
            semiMap['overdue_benjin'] = (overdueUserLoanBenJin - dfOverdue['already_pay_ben_jin'].sum())/100
            semiMap['overdue_already_pay_ben_jin'] = dfOverdue['already_pay_ben_jin'].sum()/100
            semiMap['overdueUserLoanBenJin'] = overdueUserLoanBenJin/100

        """查询数据库还款计划表 放款日期为monthStr 且当时未逾期的UserList"""
        sql = """
                -- 查询该客户还款计划表明细记录 
                -- 
                SELECT
                    t.user_name,
                    t.user_id_num,
                    t.cellphone,
                    t.loan_at,
                    t.loan_amount,
                    t.pay_ben_jin,
                    t.already_pay_ben_jin,
                    t.pay_loan_interest,
                    t.already_pay_loan_interest,
                    t.pay_platform_interest,
                    t.already_pay_platform_interest,
                    t.early_settled_handle_charge,
                    t.counted_should_pay,
                    t.overdue_days,
                    t.total_pay,
                    t.already_payed,
                    t.total_overdue_amount,
                    t.already_pay_overdue_amount,
                    t.end_date,
                    t.repay_date,
                    t.term,
                    t.contract_id,
                    t.loan_term,
                    t.repay_bank_card_num,
                    t.repay_bank_name,
                    t.overdue_status,
                    t.status
                FROM
                    qy_repayment_prod.repay_plans t
                where t.user_id_num not in (%s)
                and left(t.loan_at,7) in (%s)
                and left(t.repay_date,7)<='%s';
            """ % (overdueUserStr, monthStr, term)
        dfTerm = pdReadSql(sql)
#        print(sql)
        semiMap['total_pay_benjin'] = dfTerm['already_pay_ben_jin'].sum()/100 + semiMap['overdue_already_pay_ben_jin']
        semiMap['restBenJin'] = quarterAmount-semiMap['total_pay_benjin']
        semiMap['type'] = item
        semiMap['month'] = term
        semiMap['id'] = i
        semiMap['quarterAmount'] = quarterAmount
        semiMap['succCnt'] = succCnt
        semiMap['overdueCnt'] = overdueCnt
        semiMap['overdueRate_Cnt'] = round(100*semiMap['overdueCnt']/semiMap['succCnt'],2)
        semiMap['overdueRate_LoanBenJin'] = round(100 * semiMap['overdue_benjin'] / semiMap['quarterAmount'], 2)
        semiMap['overdueRate_restBenJin'] = round(100 * semiMap['overdue_benjin'] / semiMap['restBenJin'], 2)
        DataList.append(semiMap)
        print('to quarter:%s id:%s'%(item,term))
        i += 1
reDf = DataFrame(DataList)
saveToExcelNew('aging', reDf, workPath)
reDf.to_sql('risk_aging', engine,if_exists='replace',index=False)
saveToExcelNew('risk_aging', reDf, workPath,type='static')
time_end = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('开始时间:', time_start)
print('结束时间:', time_end)